using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using DTO;

/// Quang
/// CongtynhaohangDao
/// 
namespace DAO
{
    public class CongTyNhapHangDao: IDaoObject<CongTyNhapHangDto, int>
    {
        public CongTyNhapHangDto SaveOrUpdate(CongTyNhapHangDto obj)
        {
            return SaveOrUpdate(obj, null);
        }

        public CongTyNhapHangDto SaveOrUpdate(CongTyNhapHangDto obj, OleDbTransaction transaction)
        {
            //OleDbConnection con; = MyConnection.ConnectionData();
            OleDbCommand command = new OleDbCommand();
            //command.Connection = con;
            if (transaction != null)
            {
                command.Connection = transaction.Connection;
                command.Transaction = transaction;
            }else
            {
                command.Connection = MyConnection.ConnectionData();
            }

            int id = 0;
            try
            {
                if (obj.Id > 0 || CheckAlready(obj.TenCongTy,out id))
                {
                    //No need to update table 'cause there's no more data field
                    if (obj.Id <= 0) obj.Id = id;
                    return obj;
                }
                else
                {
                    //insert into table
                    string sqlPattern = "insert into Ctynhaphang(Tencongty) values('{0}')";
                    string sql = string.Format(sqlPattern, obj.TenCongTy);
                    command.CommandText = sql;
                    command.ExecuteNonQuery();
//                    obj.Id = MyConnection.GetnewId(transaction);
                    //lay Id lon nhat(Id cua doi tuong moi vua them vao)
                    string strSql = "Select @@IDENTITY";
                    command.CommandText = strSql;
                    command.Transaction = transaction;
                    obj.Id = (int)command.ExecuteScalar();
                    return obj;
                }
            }
            catch (Exception exception)
            {
                //if (transaction != null) transaction.Rollback();
                throw new Exception(exception.Message);
            }
        }

        public void Delete(int id)
        {
            OleDbConnection con = MyConnection.ConnectionData();
            string sqlPattern = "delete from Ctynhaphang where ID = {0}";
            string sql = string.Format(sqlPattern, id);
            OleDbCommand command = new OleDbCommand(sql, con);
            command.ExecuteNonQuery();
        }

        public CongTyNhapHangDto Select(int id)
        {
            OleDbConnection con = MyConnection.ConnectionData();
            string sql = "select ID,Tencongty from Ctynhaphang where ID=" + id;
            OleDbDataAdapter dta = new OleDbDataAdapter(sql, con);
            DataTable dataTable = new DataTable();
            dta.Fill(dataTable);
            DataRow row = dataTable.Rows[0];
            CongTyNhapHangDto congTyNhapHangDto = new CongTyNhapHangDto();
            congTyNhapHangDto.Id = Convert.ToInt32(row["ID"].ToString());
            congTyNhapHangDto.TenCongTy = (string)row["Tencongty"];
            return congTyNhapHangDto;
        }

        public bool CheckAlready(string name, out int id)
        {
            id = 0;
            OleDbConnection con = MyConnection.ConnectionData();
            string sql = "select ID from Ctynhaphang where Tencongty='" + name + "'";
            OleDbDataAdapter dta = new OleDbDataAdapter(sql, con);
            DataTable dataTable = new DataTable();
            dta.Fill(dataTable);
            if (dataTable.Rows.Count > 0)
            {
                id = Convert.ToInt32(dataTable.Rows[0]["ID"].ToString());
            }
            return id > 0;
        }

        public CongTyNhapHangDto[] SelectAll()
        {
            OleDbConnection con = MyConnection.ConnectionData();
            string sql = "select ID,Tencongty from Ctynhaphang ";
            OleDbDataAdapter dta = new OleDbDataAdapter(sql, con);
            DataTable dataTable = new DataTable();
            dta.Fill(dataTable);
            List<CongTyNhapHangDto> list = new List<CongTyNhapHangDto>();
            foreach (DataRow row in dataTable.Rows)
            {
                CongTyNhapHangDto congTyNhapHangDto = new CongTyNhapHangDto();
                congTyNhapHangDto.Id = Convert.ToInt32(row["ID"].ToString());
                congTyNhapHangDto.TenCongTy = (string)row["Tencongty"];
                list.Add(congTyNhapHangDto);
            }
            return list.ToArray();
        }

        public CongTyNhapHangDto[] SelectCongtynhaphang(DateTime tngay, DateTime dngay)
        {
            //throw new Exception("The method or operation is not implemented.");
            try
            {
                OleDbConnection cn = MyConnection.ConnectionData();
                string strSql = "SELECT Ctynhaphang.Tencongty, CTHoadonmua.Soluongmua,Duocpham.Gianhap " +
                                "FROM ((Hoadon INNER JOIN CTHoadonmua ON Hoadon.ID = CTHoadonmua.HoadonID) INNER JOIN Duocpham ON CTHoadonmua.DuocphamID = Duocpham.ID)"+
                                " INNER JOIN Ctynhaphang ON Duocpham.MaCtynhaphang = Ctynhaphang.ID "
                                + " WHERE (((Hoadon.Ngaytao)>=DateSerial(" + tngay.Year + "," + tngay.Month + "," +
                                tngay.Day + ") "
                                + " And (Hoadon.Ngaytao)<=DateSerial(" + dngay.Year + "," + dngay.Month + "," +
                                dngay.Day + "))) ";

                OleDbCommand cmd = new OleDbCommand(strSql, cn);
                OleDbDataReader dr = cmd.ExecuteReader();
                List<CongTyNhapHangDto> list = new List<CongTyNhapHangDto>();

                while (dr.Read())
                {
                    CongTyNhapHangDto ctnh = new CongTyNhapHangDto();
                    int gianhap = Int32.Parse(dr["Gianhap"].ToString());
                    ctnh.TenCongTy = dr["TenCongTy"].ToString();
                    ctnh.Soluongnhap = Int32.Parse(dr["Soluongmua"].ToString());
                    ctnh.Thanhtien = gianhap * ctnh.Soluongnhap;
                    list.Add(ctnh);
                }
                dr.Close();
                cn.Close();
                return list.ToArray();
            }
            catch (Exception exception)
            {

                throw new Exception(exception.Message);
            }


        }

    }
}
